package ecustom.finance.bean;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

import ecustom.clients.k3client.entities.ERPPAYBILLReq.FPAYBILLENTRY;
import ecustom.common.CustomMap;
import ecustom.common.conn.DB;
import ecustom.common.util.CustomUtil;

/**
* @author 作者:Gcc
* @createDate 创建时间：Aug 14, 2023 11:49:18 PM
*/
public class FinanceBean {
	
	private static final Logger log = Logger.getLogger(FinanceBean.class);
	static final String dbCode = "K3Cloud";
	
	
	public static void setInputTax(FPAYBILLENTRY newEntry, String type) {
		if("2".equals(type) ||  "20".equals(type) ||  "21".equals(type) || "32".equals(type) ) {	// 为专票的进项税类型：0：税一；1：税二
			newEntry.setF_SZXY_INPUTTAXTYPE(0);		// 税一
		}else {
			newEntry.setF_SZXY_INPUTTAXTYPE(1);	// 税二
		}
	}
	
	
	public static String subRemark(String remark) {
		if(CustomUtil.isBlank(remark)) return "";
		
		if(remark.length() > 255) {
			return remark.substring(0, 255);
		}
		
		return remark;
	}
	
	
	/**
	 * 	FBILLNO	应付单号
		FENTRYID	明细行内码
		FSEQ	明细行序号
		FCOSTID	 费用项目编码ID
		FEXPNumber	费用项目编码
		FEXPName	 费用项目名称
		FCOSTDEPARTMENTID	费用承担部门ID
		FDEPTNumber	部门编码
		FDEPTName	部门名称
		F_SZXY_FCATEGORYID	产品类别ID
		MATERIALCATEGORYNumber	产品类别编码
		MATERIALCATEGORYName	产品类别名称
		FCOMMENT	备注
		FBILLNO, FENTRYID, FSEQ, FCOSTID, FEXPNUMBER, FDEPTNUMBER, F_SZXY_FCATEGORYID, MATERIALCATEGORYNUMBER, FCOMMENT,
	 * @param fbillNo
	 * @return
	 * @throws Exception
	 */
	public static Map<Integer, CustomMap> getERPDetailData(String fbillNo) throws Exception {
		Map<Integer, CustomMap> customMap = new HashMap<Integer, CustomMap>();
		String sql = "SELECT * FROM V_K3_GETALLPAYABLE WHERE FBILLNO=?";
		List<CustomMap> listERPData = DB.conn("K3Cloud").list(sql, fbillNo);
		for(CustomMap  map: listERPData) {
			customMap.put(map.getInteger("FENTRYID"), map);
		}
		return customMap;
	}
	
	/**
	 * 根据应付单号、费用项目、费用承担部门、产品类型、备注
	 * @param fbillNo	应付单号
	 * @param fyxm	费用项目ID
	 * @param fycdbm	费用承担部门ID
	 * @param cplx	产品类型ID
	 * @param bz 
	 * * 	FBILLNO	应付单号
		FENTRYID	明细行内码
		FSEQ	明细行序号
		FCOSTID	 费用项目编码ID
		FEXPNumber	费用项目编码
		FEXPName	 费用项目名称
		FCOSTDEPARTMENTID	费用承担部门ID
		FDEPTNumber	部门编码
		FDEPTName	部门名称
		F_SZXY_FCATEGORYID	产品类别ID
		MATERTALCATEGORYNumber	产品类别编码
		MATERIALCATEGORYName	产品类别名称
		FCOMMENT	备注
	 * @param fbillNo
	 * @return
	 * @throws Exception
	 */
	public static Integer getERPDetailId(String fbillNo, String fyxm, String fycdbm, String yfsm_new) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT FENTRYID FROM V_K3_GETALLPAYABLE WHERE FBILLNO=? ");
		if(CustomUtil.isNotBlank(fyxm)) {
			sql.append(" AND FCOSTID = "+fyxm+" ");	//  费用项目编码ID
		}
		if(CustomUtil.isNotBlank(fycdbm)) {
			sql.append(" AND FCOSTDEPARTMENTID = "+fycdbm+" ");	// 费用承担部门ID
		}
	
		if(CustomUtil.isNotBlank(yfsm_new)) {
			sql.append(" AND FNUMBER = '"+yfsm_new+"' ");	// 运费说明编码
		}
		
		log.info("FinanceBean-FBillNo："+sql.toString());
		return DB.conn("K3Cloud").getInteger(sql.toString(), fbillNo);
	}
	
	
	/**
	 * 根据发起组织ID查询映射ERP组织编码
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public static String getERPOrgNumber(String id) throws Exception {
		String sql = "SELECT ERPZZ FROM  UF_ORGINFOMAP WHERE FQZZ=?";
		String number = DB.conn().getString(sql, id);
		if(CustomUtil.isBlank(number)) throw new RuntimeException("当前发起组织未维护ERP组织编码！");
		return number;
	}
	
//	/**
//	 * 根据部门的ID查询映射ERP部门编码
//	 * @param id
//	 * @return
//	 * @throws Exception
//	 */
//	public static String getERPDeptNumber(String id) throws Exception {
//		String sql = "SELECT ERPBMBM FROM UF_BMYSB WHERE ID=?";
//		String number = DB.conn().getString(sql, id);
//		return number;
//	}
	
	/**
	 * 根据部门的ID查询映射ERP部门编码
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public static String getERPDeptNumberV2(String id) throws Exception {
		String sql = "SELECT FNUMBER FROM V_K3_GETDEPT WHERE FInterId = ?";
		return DB.conn("K3Cloud").getString(sql, id);
	}

	/**
	 * 根据ERPID获取付款用途
	 * @param fid
	 * @return
	 * @throws Exception 
	 */
	public static String getERPFkyt(String fid) throws Exception {
		String sql = "SELECT FNUMBER FROM V_K3_GetRECPAYPURPOSE WHERE FInterId=?";
		return DB.conn("K3Cloud").getString(sql, fid);
	}
	
	/**
	 * 根据ERP往来单位的ID获取编码
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPWldw(String fid, String dbType) throws Exception {
		String sql = "SELECT FNUMBER FROM V_K3_GetCONTACT WHERE FInterId=? AND DBType = ?";
		return DB.conn("K3Cloud").getString(sql, fid, dbType);
	}
	
	/**
	 * 根据ERP应付单的ID获取应付单号
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPYfdh(String fid) throws Exception {
		String sql = "SELECT FBILLNO FROM V_K3_GetPAYABLE WHERE FInterId=? ";
		return DB.conn("K3Cloud").getString(sql, fid);
	}
	
//	/**
//	 * 根据ERPK3_收款人银行网点的ID获取信息
//	 * @param fid
//	 * @return
//	 * @throws Exception
//	 */
//	public static String getERPSkryhwd(String fid) throws Exception {
//		String sql = "SELECT FNAME FROM V_K3_GetBankDetail WHERE FInterId=?";
//		return DB.conn("K3Cloud").getString(sql, fid);
//	}
	
	/**
	 * 根据ERPK3_收款人银行网点的ID获取信息
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPSkryhwdV2(String lhh) throws Exception {
		String sql = "SELECT FNAME FROM V_K3_GetBankDetail WHERE LHH=?";
		return DB.conn("K3Cloud").getString(sql, lhh);
	}
	
	/**
	 * K3_银行账号
	 * 根据ERP的ID获取信息
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPYhzh(String fid) throws Exception {
		String sql = "SELECT FNUMBER FROM V_K3_GetBANKACNT WHERE FInterId=?";
		return DB.conn("K3Cloud").getString(sql, fid);
	}
	
	/**
	 * K3_产品类别
	 * 根据ERP的ID获取信息
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPCplb(String fid) throws Exception {
		String sql = "SELECT FNUMBER FROM V_K3_GetMATERIALCATEGORY WHERE FInterId=?";
		return DB.conn("K3Cloud").getString(sql, fid);
	}
	
	/**
	 * K3_供应商
	 * 根据ERP的ID获取信息
	 * @param fid 主键值
	 * @param erpOrgNumber ERP使用组织编码
	 * @return
	 * @throws Exception
	 */
	public static String getERPGys(String fid, String erpOrgNumber) throws Exception {
		String sql = "SELECT FNUMBER FROM V_K3_GetCONTACT WHERE FInterId = ? AND ForgNumber = ? AND DBType = 'BD_Supplier'";
		return DB.conn("K3Cloud").getString(sql, fid, erpOrgNumber);
	}
	
	/**
	 * K3_币别
	 * 根据ERP的ID获取信息
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPBb(String fid) throws Exception {
		String sql = "SELECT FNUMBER FROM  V_K3_GetCURRENCY WHERE FInterId=?";
		return DB.conn("K3Cloud").getString(sql, fid);
	}
	
	/**
	 * K3_费用项目
	 * 根据ERP的ID获取信息
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPFyxm(String fid) throws Exception {
		String sql = "SELECT FNUMBER FROM  V_K3_GetEXPENSE WHERE FInterId=?";
		return DB.conn("K3Cloud").getString(sql, fid);
	}
	
	/**
	 * K3_结算方式
	 * 根据ERP的ID获取信息
	 * @param fid
	 * @return
	 * @throws Exception
	 */
	public static String getERPJsfs(String fid) throws Exception {
		String sql = "SELECT FNUMBER FROM  V_K3_GetSETTLETYPE WHERE FInterId=?";
		return DB.conn("K3Cloud").getString(sql, fid);
	} 
	
	/**
	 * 
	 * @param formid
	 * @param requestid
	 * @param erpNumber
	 * @return
	 * @throws SQLException
	 */
	public static int updateERPNumber(Integer formid, String requestid, String erpNumber) throws SQLException {
		String updateSql = "UPDATE "+getTableName(formid)+" SET ERPNUMBER=? WHERE REQUESTID=?";
		return DB.conn().executeUpdate(updateSql, erpNumber, requestid);
	}
	
	/**
	 * 
	 * @param invoiceNumbers
	 * @return
	 * @throws Exception
	 */
	public static List<CustomMap> getInvoiceData(String invoiceNumbers) throws Exception {
		if(CustomUtil.isNotBlank(invoiceNumbers)) {
			String sql = "SELECT ID, BILLINGDATE, INVOICENUMBER, INVOICETYPE, INVOICESERVICEYYPE, TAX, DTAX FROM FNAINVOICELEDGER WHERE ID IN("+invoiceNumbers+")";
			return  DB.conn().list(sql);
		}
		return new ArrayList<CustomMap>();
		
	}
	
	/**
	 * 
	 * @param formid
	 * @param detailName	明细表几：DT1，DT2
	 * @param requestid
	 * @return
	 * @throws Exception
	 */
	public static Map<String, Object> getFYLFKMaxAmount(Integer formid, String detailName, Integer requestid) throws Exception {
		String tablename = getTableName(formid);
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT ID, JSHJ FROM "+tablename+"_"+detailName+" WHERE MAINID IN(SELECT ID FROM "+tablename+" WHERE REQUESTID = "+requestid+")  ");
		sb.append(" AND JSHJ IN( SELECT MAX(JSHJ) FROM "+tablename+"_DT1 WHERE MAINID IN(SELECT ID FROM "+tablename+" WHERE REQUESTID = "+requestid+")  ) ");
		return DB.conn().one(sb.toString());
	}
	
	/**
	 * 
	 * @param formid
	 * @param detailName	明细表几：DT1，DT2
	 * @param requestid
	 * @return
	 * @throws Exception
	 */
	public static Map<String, Object> getRCFYMaxAmount(Integer formid, String detailName, Integer requestid) throws Exception {
		String tablename = getTableName(formid);
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT ID, FYJE FROM "+tablename+"_"+detailName+" WHERE MAINID IN(SELECT ID FROM "+tablename+" WHERE REQUESTID = "+requestid+")  ");
		sb.append(" AND FYJE IN( SELECT MAX(FYJE) FROM "+tablename+"_DT1 WHERE MAINID IN(SELECT ID FROM "+tablename+" WHERE REQUESTID = "+requestid+")  ) ");
		return DB.conn().one(sb.toString());
	}
	
	
	/**
	 * 获取差旅报销明细备注汇总
	 * 开始时间（最小）～结束时间（最大），开始地点-结束地点（汇总去重）
	 * @throws Exception 
	 */
	public static String getCLBXDates(Integer formid, Integer requestid) throws Exception {
		String tablename = getTableName(formid);
		String minDate = DB.conn().getString(" SELECT MIN(QSRQ) FROM "+tablename+"_DT1 WHERE MAINID IN(SELECT ID FROM "+tablename+" WHERE REQUESTID = ?) ", requestid);
		String maxDate = DB.conn().getString(" SELECT MAX(QSRQ2) FROM "+tablename+"_DT1 WHERE MAINID IN(SELECT ID FROM "+tablename+" WHERE REQUESTID = ?) ", requestid);
		return minDate +"~"+maxDate;
	}
	
	public static String getInvoiceNumber(String ids) throws Exception {
		String sql = "SELECT invoiceNumber FROM V_OA_TransferInvoice WHERE ID IN("+ids+") ";
		List<String> listInvoice = DB.conn().getStringList(sql);
		if(listInvoice.size() > 0) {
			return CustomUtil.join(listInvoice, ",");
		}
		return "";
	}
	
	/**
	 * 
	 * @param formid
	 * @return
	 */
	private static String getTableName(Integer formid) {
		return "FORMTABLE_MAIN_"+Math.abs(formid);
	}
	
	public static String delHTMLTag(String htmlStr){
		if(CustomUtil.isBlank(htmlStr)) return "";
		
		htmlStr = htmlStr.replaceAll("<br>", "\n");
		
        String regEx_script = "<script[^>]*?>[\\s\\S]*?<\\/script>"; //定义script的正则表达式 
        String regEx_style = "<style[^>]*?>[\\s\\S]*?<\\/style>"; //定义style的正则表达式 
        String regEx_html = "<[^>]+>"; //定义HTML标签的正则表达式 
         
        Pattern p_script = Pattern.compile(regEx_script, Pattern.CASE_INSENSITIVE); 
        Matcher m_script = p_script.matcher(htmlStr); 
        htmlStr = m_script.replaceAll(""); //过滤script标签 
         
        Pattern p_style = Pattern.compile(regEx_style, Pattern.CASE_INSENSITIVE); 
        Matcher m_style = p_style.matcher(htmlStr); 
        htmlStr = m_style.replaceAll(""); //过滤style标签 
         
        Pattern p_html = Pattern.compile(regEx_html, Pattern.CASE_INSENSITIVE); 
        Matcher m_html = p_html.matcher(htmlStr); 
        htmlStr = m_html.replaceAll(""); //过滤html标签 
        
        // 处理特殊字符
        htmlStr = htmlStr.replaceAll("&nbsp;", " ");
        return htmlStr.trim(); //返回文本字符串 
    } 

}
